package com.expoente.model.dao.impl;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.expoente.DroidBusConstants;
import com.expoente.controller.DatabaseHelper;
import com.expoente.model.Line;
import com.expoente.model.LineInformation;
import com.expoente.model.Place;
import com.expoente.model.Route;
import com.expoente.model.dao.DBAdapterDAO;

public class DBAdapterImpl implements DBAdapterDAO {

	private final Context context;

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DBAdapterImpl(Context context) {
		this.context = context;
		DBHelper = new DatabaseHelper(context);
	}

	@Override
	public void close() {
		DBHelper.close();
	}

	@Override
	public void createLines(List<Line> lines) {
		if (!lines.isEmpty()) {
			for (Line line : lines) {
				this.insertLine(line.getName(), line.getCost());
			}
		}
	}

	@Override
	public void createPlaces(List<Place> places) {
		if (!places.isEmpty()) {
			for (Place place : places) {
				this.insertPlace(place.getAddress());
			}
		}
	}

	@Override
	public void createRoutes(List<Route> routes) {
		if (!routes.isEmpty()) {
			for (Route route : routes) {
				this.insertRoute(route.getIdLine(), route.getIdPlace(), route
						.getOrder());
			}
		}
	}

	@Override
	public Cursor getAllLines() throws SQLException {
		Cursor mCursor = db.query(true, DroidBusConstants.TABLE_LINE,
				DroidBusConstants.LINE_COLUMNS, null, null, null, null, null,
				null);
		try {
			if (mCursor != null) {
				mCursor.moveToFirst();
				if (null == ((Integer) mCursor.getInt(0)))
					throw new IndexOutOfBoundsException("Sem registros!");
			}
		} catch (IndexOutOfBoundsException e) {
			return null;
		}
		return mCursor;
	}

	@Override
	public Cursor getLineByID(long rowID) throws SQLException {
		Cursor mCursor = db.query(true, DroidBusConstants.TABLE_LINE,
				DroidBusConstants.LINE_COLUMNS, DroidBusConstants.KEY_ID + "="
						+ rowID, null, null, null, null, null);
		try {
			if (mCursor != null) {
				mCursor.moveToFirst();
				if (null == ((Integer) mCursor.getInt(0)))
					throw new IndexOutOfBoundsException("Sem registros!");
			}
		} catch (IndexOutOfBoundsException e) {
			return null;
		}
		return mCursor;
	}

	@Override
	public LineInformation getLineInformation(long lineID) throws SQLException {
		LineInformation lineInformation = new LineInformation();
		List<Route> routes = new ArrayList<Route>();
		Cursor lineCursor = getLineByID(lineID);
		if (null != lineCursor) {
			lineCursor.moveToFirst();
			Line line = new Line(lineCursor.getInt(0), lineCursor.getString(1),
					lineCursor.getDouble(2));
			lineInformation.setLine(line);
		}

		if (null != lineInformation.getLine()) {
			Cursor routeCursor = getRoutesByLineID(lineInformation.getLine()
					.get_id());
			if (null != routeCursor) {
				routeCursor.moveToFirst();
				do {
					Route route = new Route(routeCursor.getInt(0), routeCursor
							.getInt(1), routeCursor.getInt(2));
					routes.add(route);
				} while (routeCursor.moveToNext());
			}
		}

		if (null != routes && routes.size() > 0) {
			List<Place> places = new ArrayList<Place>();
			for (Route route : routes) {
				Place place = getPlaceByID(route.getIdPlace());
				if (place != null) {
					places.add(place);
				}
			}
			lineInformation.setPlaces(places);
		}

		return lineInformation;
	}

	@Override
	public Place getPlaceByID(long rowID) throws SQLException {
		Cursor mCursor = db.query(true, DroidBusConstants.TABLE_PLACE,
				DroidBusConstants.PLACE_COLUMNS, DroidBusConstants.KEY_ID + "="
						+ rowID, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
			return new Place(mCursor.getInt(0), mCursor.getString(1), mCursor
					.getDouble(2), mCursor.getDouble(3));
		}
		return null;
	}

	@Override
	public Cursor getRoutesByLineID(long lineID) throws SQLException {
		Cursor mCursor = db.query(true, DroidBusConstants.TABLE_ROUTE,
				DroidBusConstants.ROUTE_COLUMNS, DroidBusConstants.KEY_ID_LINE
						+ "=" + lineID, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	@Override
	public long insertLine(String name, double cost) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DroidBusConstants.KEY_NAME, name);
		contentValues.put(DroidBusConstants.KEY_COST, cost);
		return db.insert(DroidBusConstants.TABLE_LINE, null, contentValues);
	}

	@Override
	public long insertPlace(String address) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DroidBusConstants.KEY_ADDRESS, address);
		return db.insert(DroidBusConstants.TABLE_PLACE, null, contentValues);
	}

	@Override
	public long insertRoute(int idLine, int idPlace, int order) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DroidBusConstants.KEY_ID_LINE, idLine);
		contentValues.put(DroidBusConstants.KEY_ID_PLACE, idPlace);
		contentValues.put(DroidBusConstants.KEY_ORDER, order);
		return db.insert(DroidBusConstants.TABLE_ROUTE, null, contentValues);
	}

	@Override
	public DBAdapterImpl open() {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	@Override
	public void resetDatabase(List<Line> lines, List<Place> places,
			List<Route> routes) {

		Cursor mCursor = db.query(true, "sqlite_master",
				new String[] { "name" }, "type='table'", null, null, null,
				null, null);

		boolean flag = false;
		if (mCursor != null) {
			mCursor.moveToFirst();
			do {
				if (mCursor.getString(0).equalsIgnoreCase("line"))
					flag = true;
			} while (mCursor.moveToNext());
		}

		if (flag) {
			Cursor mCursor2 = db.query(true, DroidBusConstants.TABLE_LINE,
					DroidBusConstants.LINE_COLUMNS, DroidBusConstants.KEY_ID
							+ "=1", null, null, null, null, null);

			if (mCursor2.getCount() == 0) {
				DBHelper.dropTables(db);
				DBHelper.createTables(db);
				populateTables(lines, places, routes);
			}
		} else {
			DBHelper.createTables(db);
			populateTables(lines, places, routes);
		}
	}

	private void populateTables(List<Line> lines, List<Place> places,
			List<Route> routes) {
		createLines(lines);
		createPlaces(places);
		createRoutes(routes);
	}

	public Context getContext() {
		return context;
	}

	@Override
	public ArrayList<LineInformation> getAllLinesInformation()
			throws SQLException {
		ArrayList<LineInformation> linesInformation = new ArrayList<LineInformation>();
		Cursor cursor = getAllLines();
		if (cursor != null) {
			cursor.moveToFirst();
			do {
				LineInformation lineInformation = getLineInformation(cursor
						.getInt(0));
				linesInformation.add(lineInformation);
			} while (cursor.moveToNext());
		}
		return linesInformation;
	}

	public void updatePlaceLatLong(int _id, double latitude, double longitude) {
		ContentValues values = new ContentValues();
		values.put(DroidBusConstants.KEY_LATITUDE, latitude);
		values.put(DroidBusConstants.KEY_LONGITUDE, longitude);
		db.update(DroidBusConstants.TABLE_PLACE, values,
				DroidBusConstants.KEY_ID + "=" + _id, null);
	}

}
